﻿CREATE PROCEDURE [dbo].[spCancelSequence]
	@SequenceOrderID int,
	@CancelReasonID int
AS
	declare @SequenceStatus char(1)
	select @SequenceStatus = SequenceStatusCode
	from tblSequenceOrder where SequenceOrderID = @SequenceOrderID

	-- If the order is already canceled then just return
	if @SequenceStatus = 'X'
		return 0

	-- If the order status is not pending, paid or on backorder then throw an error
	if @SequenceStatus not in ('A', 'I' )
		raiserror( 'Sequence is not in an appropriate state to be canceled', 16, -1)

	begin transaction
	begin try
		-- Cancel the sequence.
		update tblSequenceOrder set SequenceStatusCode = 'X' where SequenceOrderID = @SequenceOrderID

		commit
	end try
	begin catch
		declare @error_message varchar(max)
		if @@TRANCOUNT > 0
			rollback transaction

		SELECT @error_message = ERROR_MESSAGE()
		RAISERROR(@error_message, 16, 1)
		return 1
	end catch

RETURN 0